/*
 * @(#)GSBaseDAO.java 
 *
 * Project: APEP-JZCG 
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   ------------ ---------- ---------------------------------------------------
 *   john        2008-08-08  First release
 *   john        2008-08-11  新增传入条件对象数组的执行方法，老方法标记为废弃
 *
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2007 Auto-Parts E-Procurement Co., Ltd. 
 *        No.251-1 Wuning Road, Shanghai, 200063, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Auto-Parts E-Procurement Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with APEP.
 *
 * Warning:
 * =============================================================================
 *   
 * 
 */
package com.hitrust.gs.lib.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.SQLLogger;
import com.hitrust.gs.service.GSBaseBean;

public class GSBaseDAO {
    /**
     * 数据库连接
     */
    protected Connection iConn = null;

    /**
     * 结果集
     */
    protected ResultSet iRs = null;

    /**
     * PreparedStatement
     */
    protected PreparedStatement iPstmt = null;

    protected static HashMap mSQLErrors = new HashMap();

    public static String popSQLError() {
        String bDebug = GSSystemConfigurator.getParam("gs.DebugMessage");

        if (bDebug == null || !bDebug.equalsIgnoreCase("true"))
            return "";

        Thread thread = Thread.currentThread();

        if (!mSQLErrors.containsKey(thread))
            return "";

        String ret = (String) mSQLErrors.get(thread);

        mSQLErrors.remove(thread);

        // new Logger()

        return ret;
    }

    public static void pushSQLError(String error) {
        String bDebug = GSSystemConfigurator.getParam("gs.DebugMessage");

        if (bDebug == null || !bDebug.equalsIgnoreCase("true"))
            return;

        mSQLErrors.put(Thread.currentThread(), error);
    }

    /**
     * 构造方法
     */
    public GSBaseDAO() {

    }

    /**
     * 开始执行一个SQL语句
     * 
     * @param aSQL
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * @return boolean 结果是否执行成功
     * @deprecated 参见 executeUpdate(String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql) 
     */
    public boolean executeUpdate(String aSql, Object[] aStmntValues) throws SQLException {
        boolean iBoolean = false;
        try {
            iConn = this.getConnetion();
            SQLLogger.log(aSql, aStmntValues);
            iPstmt = iConn.prepareStatement(aSql);
            if (aStmntValues != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
            }
            int tCount = iPstmt.executeUpdate();
            if (tCount > 0)
                iBoolean = true;
        }
        catch (SQLException e) {
            pushSQLError(e.toString());
            SQLLogger.log("error:" + e.toString());
            GSLogWriter.debug(this, aSql);
            GSLogWriter.debug(this, e);
            SQLLogger.log("");
            throw e;
        }
        finally {
            this.close();
        }
        SQLLogger.log("Success.");
        return iBoolean;
    }
    
    /**
     * 根据传入的参数产生一个带条件的SQL，并执行该SQL语句
     * 
     * @param aSQL
     *            SQL语句，注，本sql语句中不因包含查询条件相关段落
     * @param aStmntValues
     *            prepared statement的赋值条件. 如果没有, 使用 <code>null</code>
     * @param aConditionValues
     *            prepared statement的查询条件. 如果没有, 使用 <code>null</code>
     * @return boolean 结果是否执行成功
     */
    public boolean executeUpdate(String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql) throws SQLException {
        boolean tBoolean = false;
        try {
        iConn = this.getConnetion();
        tBoolean = executeUpdate(iConn,aSql,aStmntValues,aConditionValues,aTailSql);
        }catch(Exception e){
            //do noting
        }finally {
            close(iConn);
        }
        return tBoolean;
    }
    public boolean executeModifyUpdate(String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql) throws SQLException {
        boolean tBoolean = false;
        try {
        iConn = this.getConnetion();
        tBoolean = executeModifyUpdate(iConn,aSql,aStmntValues,aConditionValues,aTailSql);
        }catch(Exception e){
            //do noting
        }finally {
            close(iConn);
        }
        return tBoolean;
    }
    /**
     * 
     * @param aSql
     * @param aStmntValues
     * @param aConditionValues
     * @return
     */
    public  ArrayList reBuildSqlandStatetment(String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql){
        String newSql =  aSql;
        ArrayList tConditionValuetList = new ArrayList();
        //重构sql
        if(aConditionValues != null && aConditionValues.length>0){
            newSql =  aSql+" where 1=1";
            String tCondition = "";
            for(int i=0;i<aConditionValues.length;i++){
                if(aConditionValues[i] != null){
                    tCondition = aConditionValues[i].getConditionSql();
                    if(tCondition != null && tCondition.trim().length()!=0) {
                        newSql += " and "+tCondition;
                        Object[] tObjs = aConditionValues[i].getValues();
                        for(int j=0;j<tObjs.length;j++){
                            tConditionValuetList.add(tObjs[j]);
                        }
                    }
                }
            }
        }

        ArrayList tList = new ArrayList();
        Object[] tObjectList = null;
        if(aStmntValues != null){
            //重构参数数组
            tObjectList = new Object[tConditionValuetList.size()+aStmntValues.length];
            for(int j=0;j<tObjectList.length;j++){
                if(j<aStmntValues.length)
                    tObjectList[j] = aStmntValues[j];
                else
                    tObjectList[j] = tConditionValuetList.get(j-aStmntValues.length);
            }
        } else {
            if(tConditionValuetList.size()>0)
                tObjectList = tConditionValuetList.toArray();
        }
        
        tList.add(newSql+" "+aTailSql);
        tList.add(tObjectList);
        
        return tList;
    }
    public  ArrayList modifyBuildSqlandStatetment(String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql){
        String newSql =  aSql;
        ArrayList tConditionValuetList = new ArrayList();
        //重构sql
        if(aConditionValues != null && aConditionValues.length>0){
            newSql =  aSql+" where concentricity_code="+aStmntValues[1];
            String tCondition = "";
            for(int i=0;i<aConditionValues.length;i++){
                if(aConditionValues[i] != null){
                    tCondition = aConditionValues[i].getConditionSql();
                    if(tCondition != null && tCondition.trim().length()!=0) {
                        newSql += " and "+tCondition;
                        Object[] tObjs = aConditionValues[i].getValues();
                        for(int j=0;j<tObjs.length;j++){
                            tConditionValuetList.add(tObjs[j]);
                        }
                    }
                }
            }
        }

        ArrayList tList = new ArrayList();
        Object[] tObjectList = null;
        if(aStmntValues != null){
            //重构参数数组
            tObjectList = new Object[tConditionValuetList.size()+aStmntValues.length];
            for(int j=0;j<tObjectList.length;j++){
                if(j<aStmntValues.length)
                    tObjectList[j] = aStmntValues[j];
                else
                    tObjectList[j] = tConditionValuetList.get(j-aStmntValues.length);
            }
        } else {
            if(tConditionValuetList.size()>0)
                tObjectList = tConditionValuetList.toArray();
        }
        
        tList.add(newSql+" "+aTailSql);
        tList.add(tObjectList);
        
        return tList;
    }
    /**
     * 开始执行一个SQL语句
     * 
     * @param aConnection
     *            数据库连接
     * @param aSQL
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * @return boolean 结果是否执行成功
     * @deprecated 参见executeUpdate(Connection aConnection, String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql)
     */
    public boolean executeUpdate(Connection aConnection, String aSql, Object[] aStmntValues) throws SQLException {
        boolean iBoolean = false;
        try {
            if (aConnection != null) {
                SQLLogger.log(aSql, aStmntValues);
                iPstmt = aConnection.prepareStatement(aSql);
                if (aStmntValues != null) {
                    GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
                }
                int tCount = iPstmt.executeUpdate();
                if (tCount > 0)
                    iBoolean = true;
            }
            else {
                GSLogWriter.error(this, "DB Connection is null");
            }
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            close(iPstmt);
        }
        SQLLogger.log("success.");
        return iBoolean;
    }
    
    
    /**
     * 开始执行一个SQL语句
     * 
     * @param aConnection
     *            数据库连接
     * @param aSQL
     *            SQL语句 注，本sql语句中不因包含查询条件相关段落
     * @param aStmntValues
     *            prepared statement的赋值条件. 如果没有, 使用 <code>null</code>
     * @param aConditionValues
     *            prepared statement的查询条件. 如果没有, 使用 <code>null</code>
     * @return boolean 结果是否执行成功
     */
    public boolean executeUpdate(Connection aConnection, String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql) throws SQLException {
        boolean iBoolean = false;
        try {
            if (aConnection != null) {
                //根据参数重构sql和条件数组
                ArrayList tList = reBuildSqlandStatetment(aSql,aStmntValues,aConditionValues,aTailSql);
                
                SQLLogger.log((String)tList.get(0), (Object[])tList.get(1));
                iPstmt = aConnection.prepareStatement((String)tList.get(0));
                if (tList.get(1) != null) {
                    GSPreparedStatementFactory.buildStatement(iPstmt, (Object[])tList.get(1));
                }
                int tCount = iPstmt.executeUpdate();
                if (tCount > 0)
                    iBoolean = true;
            }
            else {
                GSLogWriter.error(this, "DB Connection is null");
            }
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            close(iPstmt);
        }
        SQLLogger.log("success.");
        return iBoolean;
    }
    public boolean executeModifyUpdate(Connection aConnection, String aSql, Object[] aStmntValues, ConditionValueObj[] aConditionValues,String aTailSql) throws SQLException {
        boolean iBoolean = false;
        try {
            if (aConnection != null) {
                //根据参数重构sql和条件数组
                ArrayList tList = modifyBuildSqlandStatetment(aSql,aStmntValues,aConditionValues,aTailSql);
                
                SQLLogger.log((String)tList.get(0), (Object[])tList.get(1));
                iPstmt = aConnection.prepareStatement((String)tList.get(0));
                if (tList.get(1) != null) {
                    GSPreparedStatementFactory.buildStatement(iPstmt, (Object[])tList.get(1));
                }
                int tCount = iPstmt.executeUpdate();
                if (tCount > 0)
                    iBoolean = true;
            }
            else {
                GSLogWriter.error(this, "DB Connection is null");
            }
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            close(iPstmt);
        }
        SQLLogger.log("success.");
        return iBoolean;
    }
    
    /**
     * 
     * @param aSql 注，本sql语句中不因包含查询条件相关段落
     * @param aStmntValues
     * @param aConditionValues
     * @return
     */
    public  ArrayList reBuildSqlandStatetment(String aSql, ConditionValueObj[] aConditionValues,String aTailSql){
        String newSql =  aSql;
        //重构sql
        ArrayList tConditionValuetList = new ArrayList();
        if(aConditionValues != null && aConditionValues.length>0){
            newSql =  aSql+" where 1=1";
            String tCondition = "";
            for(int i=0;i<aConditionValues.length;i++){
                if( aConditionValues[i] != null) {
                    tCondition = aConditionValues[i].getConditionSql();
                    if(tCondition != null && tCondition.trim().length()!=0) {
                        newSql += " and "+tCondition;
                        Object[] tObjs = aConditionValues[i].getValues();
                        for(int j=0;j<tObjs.length;j++){
                            tConditionValuetList.add(tObjs[j]);
                        }
                    }
                }
            }
        }
        ArrayList tList = new ArrayList();
        Object[] tObjectList = null;
        if(tConditionValuetList.size()>0){
            tObjectList = tConditionValuetList.toArray();
        }
        //重构参数数组
        tList.add(newSql+" "+aTailSql);
        tList.add(tObjectList);
        
        return tList;
    }
    
    /**
     * 开始执行一个SQL查询
     * 
     * @param aConnection
     *            数据库连接
     * @param aBean
     *            结果映射处理程序
     * @param aSQL
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有�?, 使用 <code>null</code>
     * @return ArrayList DTO结果数组列表
     * @deprecated 参见executeQuery(Connection aConnection, GSBaseBean aBean, String aSql,  ConditionValueObj[] aConditionValues,String aTailSql)
     */
    public ArrayList executeQuery(Connection aConnection, GSBaseBean aBean, String aSql, Object[] aStmntValues) throws SQLException, Exception {

        try {
            SQLLogger.log(aSql, aStmntValues);
            iPstmt = aConnection.prepareStatement(aSql);

            // 组合Statement条件.
            if (aStmntValues != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
            }

            // 执行
            iRs = iPstmt.executeQuery();

            ArrayList tArrayList = new ArrayList();

            while (iRs.next()) {
                tArrayList.add(aBean.process(iRs));
            }
            // 返回结果数组列表
            SQLLogger.log("Success");
            return tArrayList;
        }
        catch (SQLException e) {
            // 抛出�?个数据库查询例外
            pushSQLError(e.toString());
            SQLLogger.log("error:" + e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            close(iRs);
            close(iPstmt);
        }
    }
    
    /**
     * 开始执行一个SQL查询
     * 
     * @param aConnection
     *            数据库连接
     * @param aBean
     *            结果映射处理程序
     * @param aSQL 注，本sql语句中不因包含查询条件相关段落
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有�?, 使用 <code>null</code>
     * @return ArrayList DTO结果数组列表
     */
    public ArrayList executeQuery(Connection aConnection, GSBaseBean aBean, String aSql,  ConditionValueObj[] aConditionValues,String aTailSql) throws SQLException, Exception {

        try {
            ArrayList tList = reBuildSqlandStatetment(aSql, aConditionValues,aTailSql);
            SQLLogger.log((String)tList.get(0), (Object[])tList.get(1));
            iPstmt = aConnection.prepareStatement((String)tList.get(0));

            // 组合Statement条件.
            if (tList.get(1) != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, (Object[])tList.get(1));
            }

            // 执行
            iRs = iPstmt.executeQuery();
            ArrayList tArrayList = new ArrayList();

            while (iRs.next()) {
                tArrayList.add(aBean.process(iRs));
            }
            // 返回结果数组列表
            SQLLogger.log("Success");
            return tArrayList;
        }
        catch (SQLException e) {
            // 抛出�?个数据库查询例外
            pushSQLError(e.toString());
            SQLLogger.log("error:" + e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            close(iRs);
            close(iPstmt);
        }
    }

    /**
     * 开始执行一个SQL查询
     * 
     * @param aBean
     *            结果映射处理程序
     * @param aSQL
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * @return ArrayList DTO结果数组列表
     * @deprecated 参见  executeQuery(GSBaseBean aBean, String aSql,ConditionValueObj[] aConditionValues,String aTailSql)
     */

    public ArrayList executeQuery(GSBaseBean aBean, String aSql, Object[] aStmntValues) throws SQLException, Exception {
        try {
            iConn = this.getConnetion();
            SQLLogger.log(aSql, aStmntValues);
            iPstmt = iConn.prepareStatement(aSql);

            // 组合Statement条件.
            if (aStmntValues != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
            }

            // 执行
            iRs = iPstmt.executeQuery();

            ArrayList tArrayList = new ArrayList();
            while (iRs.next()) {
                tArrayList.add(aBean.process(iRs));
            }
            SQLLogger.log("Success");
            // 返回结果数组列表
            return tArrayList;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            this.close();
        }
    }
    
    /**
     * 开始执行一个SQL查询
     * 
     * @param aBean
     *            结果映射处理程序
     * @param aSQL 注，本sql语句中不因包含查询条件相关段落
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * @return ArrayList DTO结果数组列表
     */

    public ArrayList executeQuery(GSBaseBean aBean, String aSql,ConditionValueObj[] aConditionValues,String aTailSql) throws SQLException, Exception {
        ArrayList tList = new ArrayList();
        try {    
            iConn = this.getConnetion();
            tList = executeQuery(iConn, aBean, aSql,  aConditionValues,aTailSql);

        }catch(Exception e){
            //do noting
        }finally {
            close(iConn);
        }
        return tList;
    }

    /**
     * 开始执行一个带分页功能的SQL查询
     * 
     * @param aBean
     *            结果映射处理程序
     * @param aSQL
     *            SQL语句
     * @param aStmntValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * @param pageNum
     *            当前查询的页号（页号从1开始）
     * @param pageSize
     *            每页的行数
     * 
     * @return ArrayList DTO结果数组列表
     * @deprecated  参见executeQuery(GSBaseBean aBean, String aSql,  ConditionValueObj[] aConditionValues,String aTailSql, int aPageNum, int aPageSize)
     */

    public ArrayList executeQuery(GSBaseBean aBean, String aSql, Object[] aStmntValues, int aPageNum, int aPageSize) throws SQLException, Exception {
        iConn = this.getConnetion();
        try {
            ArrayList tList = executeQuery(iConn,aBean, aSql,  aStmntValues, aPageNum, aPageSize);
            return tList;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            this.close();
        }
    }
    
    /**
     * 开始执行一个带分页功能的SQL查询
     * 
     * @param aBean
     *            结果映射处理程序
     * @param aSQL
     *            SQL语句 注，本sql语句中不因包含查询条件相关段落
     * @param aConditionValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * @param pageNum
     *            当前查询的页号（页号从1开始）
     * @param pageSize
     *            每页的行数
     * 
     * @return ArrayList DTO结果数组列表
     */

    public ArrayList executeQuery(GSBaseBean aBean, String aSql,  ConditionValueObj[] aConditionValues,String TailSql, int aPageNum, int aPageSize) throws SQLException, Exception {
        iConn = this.getConnetion();
        try {
            ArrayList tList = executeQuery(iConn,aBean, aSql,  aConditionValues,TailSql, aPageNum, aPageSize);
            return tList;
        }catch(SQLException se){
            throw se;
        }catch(Exception e){
            throw e;
        }
        finally{
            this.close();
        }

    }

    /**
     * 
     * @param aConn
     * @param aBean
     * @param aSql
     * @param aStmntValues
     * @param aPageNum
     * @param aPageSize
     * @return
     * @throws SQLException
     * @throws Exception
     * @deprecated 参见 executeQuery(Connection aConn, GSBaseBean aBean, String aSql, ConditionValueObj[] aConditionValues,String aTailSql, int aPageNum, int aPageSize)
     */
    public ArrayList executeQuery(Connection aConn, GSBaseBean aBean, String aSql, Object[] aStmntValues, int aPageNum, int aPageSize) throws SQLException,
            Exception {
        try {
            SQLLogger.log(aSql, aStmntValues);
            int tStartRecord =(aPageNum-1)*aPageSize;
            int tEndRecord   =(aPageNum)*aPageSize+1;
            String tSql = "select * from (select gta.*,rownum r from ("+aSql+") gta) gtb where gtb.r >"+tStartRecord+" and gtb.r<"+tEndRecord;
            iPstmt = aConn.prepareStatement(tSql);

            // 组合Statement条件.
            if (aStmntValues != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
            }

            // 执行
            iRs = iPstmt.executeQuery();
            ArrayList tArrayList = new ArrayList();
            while (iRs.next()) {
                tArrayList.add(aBean.process(iRs));
            }
            SQLLogger.log("Success");
            // 返回结果数组列表
            return tArrayList;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            close(iRs);
            close(iPstmt);
        }
    }
    
    /**
     * 
     * @param aConn
     * @param aBean
     * @param aSql 注，本sql语句中不因包含查询条件相关段落
     * @param aStmntValues
     * @param aPageNum
     * @param aPageSize
     * @return
     * @throws SQLException
     * @throws Exception
     */
    public ArrayList executeQuery(Connection aConn, GSBaseBean aBean, String aSql, ConditionValueObj[] aConditionValues, String aTailSql,int aPageNum, int aPageSize) throws SQLException,
            Exception {
        try {
            ArrayList tList = reBuildSqlandStatetment(aSql, aConditionValues,aTailSql);
            SQLLogger.log((String)tList.get(0), (Object[])tList.get(1));
            
            int tStartRecord =(aPageNum-1)*aPageSize;
            int tEndRecord   =(aPageNum)*aPageSize+1;
            String tSql = "select * from (select gta.*,rownum r from ("+(String)tList.get(0)+") gta) gtb where gtb.r >"+tStartRecord+" and gtb.r<"+tEndRecord;
            iPstmt = aConn.prepareStatement(tSql);

            // 组合Statement条件.
            if ((Object[])tList.get(1) != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, (Object[])tList.get(1));
            }

            // 执行
            iRs = iPstmt.executeQuery();
            ArrayList tArrayList = new ArrayList();
            while (iRs.next()) {
                tArrayList.add(aBean.process(iRs));
            }
            SQLLogger.log("Success");
            // 返回结果数组列表
            return tArrayList;
        } catch (SQLException e) {
            SQLLogger.log("error:" + e.getLocalizedMessage());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        } catch(Error error) {
            SQLLogger.log("error:" + error.getLocalizedMessage());
            pushSQLError(error.toString());
            GSLogWriter.debug(this, error);
            GSLogWriter.debug(this, aSql);
            throw error;   
        } finally {
            close(iRs);
            close(iPstmt);
        }
    }
    /**
     * 在分页相关功能中，用于查询符合条件的记录的数量
     * 
     * @param aSQL
     *            SQL语句，必须使用以下格式的SQL语句，比如："Select count(*) from table_name
     *            where_clause"
     * @param aStmntValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * 
     * @return int 符合条件的数据库记录条数
     * @deprecated 参见queryRowNum(String aSql,ConditionValueObj[] aConditionValues)
     */

    public int queryRowNum(String aSql, Object[] aStmntValues) throws SQLException, Exception {
        try {
            iConn = this.getConnetion();
            SQLLogger.log(aSql, aStmntValues);
            iPstmt = iConn.prepareStatement(aSql);

            // 组合Statement条件.
            if (aStmntValues != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
            }

            // 执行
            iRs = iPstmt.executeQuery();

            int tRows = 0;
            if ((iRs != null) && iRs.next()) {
                tRows = iRs.getInt(1);
            }

            SQLLogger.log("Success");
            return tRows;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            this.close();
        }
    }

    /**
     * 在分页相关功能中，用于查询符合条件的记录的数量
     * 
     * @param aSQL
     *            SQL语句，必须使用以下格式的SQL语句，比如："Select count(*) from table_name"
     * @param aConditionValues
     *            prepared statement的条件. 如果没有, 使用 <code>null</code>
     * 
     * @return int 符合条件的数据库记录条数
     */

    public int queryRowNum(String aSql,ConditionValueObj[] aConditionValues, String aTailSql) throws SQLException, Exception {
        try {
            iConn = this.getConnetion();
            ArrayList tList = reBuildSqlandStatetment(aSql, aConditionValues,aTailSql);
            SQLLogger.log((String)tList.get(0), (Object[])tList.get(1));
            iPstmt = iConn.prepareStatement((String)tList.get(0));

            // 组合Statement条件.
            if ((Object[])tList.get(1) != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, (Object[])tList.get(1));
            }

            // 执行
            iRs = iPstmt.executeQuery();

            int tRows = 0;
            if ((iRs != null) && iRs.next()) {
                tRows = iRs.getInt(1);
            }

            SQLLogger.log("Success");
            return tRows;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            this.close();
        }
    }
    /**
     * 得到指定值为double型
     * 
     * @param aSql
     *            SQL语句，必须使用以下格式的SQL语句，比如："Select count(*) from table_name
     *            where_clause"
     * @param aStmntValues
     * @return
     * @throws SQLException
     * @throws Exception
     * @deprecated 参见queryRowNumByDouble(String aSql,ConditionValueObj[] aConditionValues)
     */
    public double queryRowNumByDouble(String aSql, Object[] aStmntValues) throws SQLException, Exception {
        try {
            iConn = this.getConnetion();
            SQLLogger.log(aSql, aStmntValues);
            iPstmt = iConn.prepareStatement(aSql);

            // 组合Statement条件.
            if (aStmntValues != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, aStmntValues);
            }

            // 执行
            iRs = iPstmt.executeQuery();

            double value = 0;
            if ((iRs != null) && iRs.next()) {
                value = iRs.getDouble(1);
            }
            SQLLogger.log("Success");
            return value;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            this.close();
        }
    }
    
    /**
     * 得到指定值为double型
     * 
     * @param aSql
     *          SQL语句，必须使用以下格式的SQL语句，比如："Select count(*) from table_name"
     * @param aConditionValues
     * @return
     * @throws SQLException
     * @throws Exception
     */
    public double queryRowNumByDouble(String aSql,ConditionValueObj[] aConditionValues, String aTailSql) throws SQLException, Exception {
        try {
            iConn = this.getConnetion();
            ArrayList tList = reBuildSqlandStatetment(aSql, aConditionValues,aTailSql);
            SQLLogger.log((String)tList.get(0), (Object[])tList.get(1));
            iPstmt = iConn.prepareStatement((String)tList.get(0));

            // 组合Statement条件.
            if ((Object[])tList.get(1) != null) {
                GSPreparedStatementFactory.buildStatement(iPstmt, (Object[])tList.get(1));
            }

            // 执行
            iRs = iPstmt.executeQuery();

            double value = 0;
            if ((iRs != null) && iRs.next()) {
                value = iRs.getDouble(1);
            }
            SQLLogger.log("Success");
            return value;
        }
        catch (SQLException e) {
            SQLLogger.log("error:" + e.toString());
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.debug(this, aSql);
            throw e;
        }
        finally {
            this.close();
        }
    }

    /**
     * 取得Connetion连接
     * 
     * @return
     */
    public Connection getConnetion() {
        DBConnectionManager tDBConn = DBConnectionManager.getInstance();
        try {
            iConn = tDBConn.getConnection();
            return iConn;
        }
        catch (SQLException e) {
            pushSQLError(e.toString());
            GSLogWriter.debug(this, e);
            GSLogWriter.error(this, e.getMessage());
            return null;
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 关闭连接
     * 
     */
    public void close() {
        if (iRs != null) {
            try {
                iRs.close();
                iRs = null;
            }
            catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        if (iPstmt != null) {
            try {
                iPstmt.close();
                iPstmt = null;
            }
            catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        try {
            if (iConn != null && !iConn.isClosed()) {
                iConn.close();
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    /**
     * 关闭数据库连接
     * 
     * @param Connection
     *            connection 数据库连接
     * @return �?
     */
    public static void close(Connection connection) {
        if (connection == null)
            return;
        try {
            if (connection.isClosed()) {
                return;
            }

            connection.close();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 关闭ResultSet
     * 
     * @param ResultSet
     *            resultSet
     * @return �?
     */
    public static void close(ResultSet resultSet) {
        if (resultSet == null)
            return;

        try {
            resultSet.close();
            resultSet = null;
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 关闭数据声明
     * 
     * @param Statement
     *            statement
     * @return �?
     */
    public static void close(Statement statement) {
        if (statement == null)
            return;

        try {
            statement.close();
            statement = null;
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 关闭数据声明
     * 
     * @param PreparedStatement
     *            prestatement
     * @return �?
     */
    public static void close(PreparedStatement prestatement) {
        if (prestatement == null)
            return;

        try {
            prestatement.close();
            prestatement = null;
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 执行该数据库连接的更新提示
     * 
     * @param Connection
     *            connection 数据库连接
     */
    public static void commit(Connection connection) {
        if (connection == null)
            return;
        try {
            connection.commit();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 执行该数据库连接的回�?
     * 
     * @param Connection
     *            connection 数据库连接
     */
    public static void rollback(Connection connection) {
        if (connection == null)
            return;

        try {
            connection.rollback();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 设置该数据库连接的自动提交模式
     * 
     * @param connection
     *            数据库连接
     * @param flag
     *            自动提交模式(true/false)
     * @return Connection 经过设定的数据库连接
     */
    public static Connection setAutoCommit(Connection connection, boolean flag) {
        try {
            connection.setAutoCommit(flag);
            return connection;
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {

    }

}